package com.zust.ysc012.dao;

import com.zust.ysc012.entity.Project;
import com.zust.ysc012.entity.Table;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.zust.ysc012.utility.DBUtil.*;

/**
 * @Description
 * @Author Mato
 * @Date 01/07/2021 14:25
 */
public class PublicDao {
    public static boolean select_number_sql(String account) throws SQLException {
        String sql = "SELECT number \n" +
                "FROM school";
        Object[] params = new Object[1];
        params[0] = account;
        ResultSet rs;
        rs = executeSelect(sql, params);
        if (rs.next()) {
            pre.close();
            con.close();
            return true;
        } else {
            pre.close();
            con.close();
            return false;
        }
    }

    public static int select_sign_sql(String account) throws SQLException {
        String sql = "SELECT sign\n" +
                "FROM school\n" +
                "WHERE number = ?";
        Object[] params = new Object[1];
        params[0] = account;
        ResultSet rs;
        int i = 0;
        rs = executeSelect(sql, params);
        while (rs.next()) {
            i = rs.getInt(1);
        }
        pre.close();
        con.close();
        return i;
    }

    public static List<Project> select_all_project_sql() throws SQLException {
        String sql = "SELECT *\n" +
                "FROM project";
        ResultSet rs;
        List<Project> projects = new ArrayList<>();
//        Project project = new Project();
        Object[] params = new Object[0];
        rs = executeSelect(sql, params);
        while (rs.next()) {
            Project project = new Project();
            project.setProject_number(rs.getInt(1));
            project.setTeacher_number(rs.getString(2));
            project.setName(rs.getString(3));
            project.setWin(rs.getInt(4));
            project.setStarting_time(rs.getDate(5));
            project.setDeadline(rs.getDate(6));
            projects.add(project);
        }
        pre.close();
        con.close();
        return projects;
    }

    public static String select_project_name(int project_number) throws SQLException {
        String sql = "SELECT name\n" +
                "FROM project\n" +
                "WHERE project_number = ?";
        Object[] params = new Object[1];
        params[0] = project_number;
        ResultSet rs;
        String name = null;
        rs = executeSelect(sql, params);
        while (rs.next()) {
            name = rs.getString(1);
        }
        pre.close();
        con.close();
        return name;
    }

    public static Date select_project_startTime(int project_number) throws SQLException {
        String sql = "SELECT starting_time\n" +
                "FROM project\n" +
                "WHERE project_number = ?";
        Object[] params = new Object[1];
        params[0] = project_number;
        ResultSet rs;
        Date name = null;
        rs = executeSelect(sql, params);
        while (rs.next()) {
            name = rs.getDate(1);
        }
        pre.close();
        con.close();
        return name;
    }

    public static Date select_project_deadline(int project_number) throws SQLException {
        String sql = "SELECT deadline\n" +
                "FROM project\n" +
                "WHERE project_number = ?";
        Object[] params = new Object[1];
        params[0] = project_number;
        ResultSet rs;
        Date name = null;
        rs = executeSelect(sql, params);
        while (rs.next()) {
            name = rs.getDate(1);
        }
        pre.close();
        con.close();
        return name;
    }

    public static ResultSet select_table_student_sql() throws SQLException {
        String sql = "SELECT *\n" +
                "FROM student_project";
        ResultSet rs;
        Object[] params = new Object[0];
        rs = executeSelect(sql, params);
        return rs;
    }

    public static void nowUser(String number) throws SQLException {
        String sql = "INSERT INTO nowuser (number)\n" +
                "VALUES (?)";
        Object[] params = new Object[1];
        params[0] = number;
        executeUpdate(sql, params);
    }

    public static String getNowUser() throws SQLException {
        String sql = "SELECT number\n" +
                "FROM nowuser";
        Object[] params = new Object[0];
        ResultSet rs;
        String number = null;
        rs = executeSelect(sql, params);
        while (rs.next()) {
            number = rs.getString(1);
        }
        return number;
    }

    public static void deleteNowUser() throws SQLException {
        String sql = "truncate table nowuser";
        Object[] params = new Object[0];
        executeUpdate(sql, params);
    }

    public static List search_sql(String search) throws SQLException {
        String sql = "SELECT\n" +
                "\tproject_number \n" +
                "FROM\n" +
                "\tproject \n" +
                "WHERE\n" +
                "\tNAME LIKE ? \n" +
                "\tOR teacher_number IN ( SELECT number FROM school WHERE NAME LIKE ? );";
        Object[] params = new Object[2];
        List search_number = new ArrayList();
        params[0] = "%" + search + "%";
        params[1] = "%" + search + "%";
        ResultSet rs;
        rs = executeSelect(sql, params);
        while (rs.next()) {
            System.out.println("aim1");
            search_number.add(rs.getInt(1));
            System.out.println("sim2");
        }
        System.out.println("zhongg");
        pre.close();
        con.close();
        System.out.println(search_number);
        return search_number;
    }
}
